
/*
{
"状态":1,
"名称":"导入-表结构",
"作者":"马学兵",
"创建时间":"2018-07-20",
"功能":"导入-表结构",
"log": [
	{"date":"" , "dev" :"" , "content" : "前端判断表数据是否存在,如果存在提示是否重新导入"}
]
}
*/

DECLARE @table_name varchar(250)
DECLARE @describe nvarchar(500)
DECLARE @project nvarchar(200)
DECLARE @table_key INT

 
Set @table_name='{table_name}'
Set @describe='{describe}'
Set @project='{project}'

if isnull(@table_name,'')=''
begin 
	RAISERROR ('SQL:表名不允许为空', 16 ,1 ) ; 
	return 
end 

if isnull(@describe,'')=''
begin 
	RAISERROR ('SQL:描述不允许为空', 16 ,1 ) ; 
	return 
end 

if isnull(@project,'')=''
begin 
	RAISERROR ('SQL:项目不允许为空', 16 ,1 ) ; 
	return 
end 

if object_id(@table_name) is null
begin  
	RAISERROR ('SQL:该表不存在,请先创建', 16 ,1 ) ; 
	return 
end
 
if exists(select 1 from y_table where table_name=@table_name)
begin
	delete from y_columns 
	FROM y_columns
	inner join y_table ON  y_table.table_key = y_columns.table_key
	WHERE table_name=@table_name
	delete from y_table where table_name=@table_name
end 

INSERT INTO dbo.y_table(table_name,describe,project,remarks,create_time)
VALUES(@table_name,@describe,@project,NULL,GETDATE())
SET @table_key=@@IDENTITY


INSERT INTO dbo.y_columns(table_key,colorder,colname,col_des,datatype,collength,scale,isidentity,iskey,isnullable,defvalue,create_time)
SELECT @table_key,
       col.colorder AS [colorder],
       col.name AS [colname],
       NULL,
       t.name AS [datatype],
       col.length AS [collength],
       ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS [scale],
         COLUMNPROPERTY(col.id, col.name, 'IsIdentity')  AS [isidentity],
       CASE
           WHEN EXISTS
                (
                    SELECT 1
                    FROM dbo.sysindexes si
                        INNER JOIN dbo.sysindexkeys sik
                            ON si.id = sik.id
                               AND si.indid = sik.indid
                        INNER JOIN dbo.syscolumns sc
                            ON sc.id = sik.id
                               AND sc.colid = sik.colid
                        INNER JOIN dbo.sysobjects so
                            ON so.name = si.name
                               AND so.xtype = 'PK'
                    WHERE sc.id = col.id
                          AND sc.colid = col.colid
                ) THEN
               '1'
           ELSE
               '0'
       END AS [iskey],
       col.isnullable  AS [isnullable],
       comm.text AS [defvalue],
       GETDATE() AS date 
FROM dbo.syscolumns col
    LEFT JOIN dbo.systypes t
        ON col.xtype = t.xusertype
    INNER JOIN dbo.sysobjects obj
        ON col.id = obj.id
           AND obj.xtype = 'U'
           AND obj.status >= 0
    LEFT JOIN dbo.syscomments comm
        ON col.cdefault = comm.id
    LEFT JOIN sys.extended_properties ep
        ON col.id = ep.major_id
           AND col.colid = ep.minor_id
           AND ep.name = 'MS_Description'
    LEFT JOIN sys.extended_properties epTwo
        ON obj.id = epTwo.major_id
           AND epTwo.minor_id = 0
           AND epTwo.name = 'MS_Description'
WHERE obj.name = @table_name
ORDER BY col.colorder;